﻿IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_RCBCJL]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_RCBCJL]
GO

/**********

[版本号]1.0.0
[创建时间]2018-12-15
[作者]Z_DD
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--日常病程记录GXBZ_EMR_RCBCJL数据上传
[返回值]
[调用的USP]
	[调用实例] EXEC USP_GXBZ_EMR_RCBCJL
[修改历史]
**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_RCBCJL]    
AS 
  SET XACT_ABORT ON 
DECLARE 
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN


	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_RCBCJL')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_RCBCJL','2015-07-01')
	END
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_RCBCJL'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM EMR_RCBCJL(NOLOCK)

	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN
	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_RCBCJL(
		JLLSH		varchar(64)          not null,
		XGBZ		varchar(1)           not null,
		GRJBXXBSH	varchar(64)          not null,
		CXJMJKDABH	varchar(17)          not null,
		CJSJ		datetime             null,
		WDCJZGH		varchar(64)          null,
		WDCJZXM		varchar(30)          null,
		WDGLJGDM	varchar(22)          null,
		WDGLJGMC	varchar(70)          null,
		QMRQSJ		datetime             null,
		YSGH		varchar(64)          null,
		YSXM		varchar(30)          null,
		ZYJSZWLBDM	varchar(30)          null,
		ZYJSZWLBMC	varchar(100)         null,
		JLRQSJ		datetime             null,
		BCH			varchar(20)          null,
		BCHMC		varchar(100)         null,
		BFH			varchar(20)          null,
		BFHMC		varchar(100)         null,
		KSBM		varchar(20)          null,
		KSMC		varchar(100)         null,
		BQBM		varchar(20)          null,
		BQMC		varchar(100)         null,
		YLJGDM		varchar(22)          null,
		YLJGMC		varchar(70)          null,
		ZYBC		varchar(4000)        null,
		ZYSZGCJG	varchar(1024)        null,
		YZNR		varchar(1024)        null,
		BZLZ		varchar(1024)        null,
		ZYJZFF		varchar(100)         null,
		ZYYYFF		varchar(100)         null,
		MJ			varchar(16)          not null,
		TBRQ		datetime             not null,
		SCZT		varchar(1)           not null,
		CS_JYBZ		int                  not null,
		SCSJ		datetime             not null
   )ON [PRIMARY]
   
	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_RCBCJL (
		JLLSH,
		XGBZ,
		GRJBXXBSH,
		CXJMJKDABH,
		CJSJ,
		WDCJZGH,
		WDCJZXM,
		WDGLJGDM,
		WDGLJGMC,
		QMRQSJ,
		YSGH,
		YSXM,
		ZYJSZWLBDM,
		ZYJSZWLBMC,
		JLRQSJ,
		BCH,
		BCHMC,
		BFH,
		BFHMC,
		KSBM,
		KSMC,
		BQBM,
		BQMC,
		YLJGDM,
		YLJGMC,
		ZYBC,
		ZYSZGCJG,
		YZNR,
		BZLZ,
		ZYJZFF,
		ZYYYFF,
		MJ,
		TBRQ,
		SCZT,
		CS_JYBZ,
		SCSJ)
	SELECT
		A.RCBCJLLSH JLLSH,
		A.XGBZ XGBZ,
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = B.ZJHM) GRJBXXBSH,
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = B.ZJHM) CXJMJKDABH,
		A.JLRQSJ CJSJ,
		'' WDCJZGH,
		'' WDCJZXM,
		'500103000000001' WDGLJGDM,
		'渝中区卫计委' WDGLJGMC,
		A.QMRQSJ QMRQSJ,
		A.YSGH YSGH,
		A.YSQM YSXM,
		A.ZYJSZWLBDM ZYJSZWLBDM,
		(SELECT [XMMC] FROM CV08_30_005 WHERE A.ZYJSZWLBDM=XMDM )ZYJSZWLBMC,
		A.JLRQSJ JLRQSJ,
		A.BCH BCH,
		A.BCH+'病床' BCHMC,
		A.BFH BFH,
		A.BFH+'病房' BFHMC,
		A.KSBM KSBM,
		A.KSMC KSMC,
		'' BQBM,
		A.BQMC BQMC,
		isnull(C.SPTDM,'500103000000001')  YLJGDM,
		isnull(C.JGMC,'渝中区卫计委') YLJGMC,
		A.ZYBC ZYBC,
		A.ZYSZGCJG ZYSZGCJG,
		A.YZNR YZNR,
		A.BZLZ BZLZ,
		A.ZYJZFF  ZYJZFF,
		A.ZYYYFF ZYYYFF,
		A.MJ MJ,
		A.TBRQ TBRQ,
		'0' AS SCZT,
		0 AS CS_JYBZ,
		GETDATE() AS SCSJ	
	FROM EMR_RCBCJL A(NOLOCK)
	LEFT JOIN ZYJZJLB B(NOLOCK) ON A.ZYJZLSH=B.JZLSH
	LEFT JOIN SYS_ZCJG_DZ C(NOLOCK) ON A.YLJGDM=C.QPTDM
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01') 
	and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = B.ZJHM) is not NULL
	
	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_RCBCJL A,#TEMP_GXBZ_EMR_RCBCJL B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.YLJGDM=B.YLJGDM AND A.JLLSH=B.JLLSH
	
	--插入数据至中间库
	INSERT INTO GXBZ_EMR_RCBCJL(
		JLLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,CJSJ,WDCJZGH,WDCJZXM,WDGLJGDM,WDGLJGMC,QMRQSJ,YSGH,YSXM,
		ZYJSZWLBDM,ZYJSZWLBMC,JLRQSJ,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,BQBM,BQMC,YLJGDM,YLJGMC,ZYBC,
		ZYSZGCJG,YZNR,BZLZ,ZYJZFF,ZYYYFF,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ)
	SELECT
		JLLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,CJSJ,WDCJZGH,WDCJZXM,WDGLJGDM,WDGLJGMC,QMRQSJ,YSGH,YSXM,
		ZYJSZWLBDM,ZYJSZWLBMC,JLRQSJ,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,BQBM,BQMC,YLJGDM,YLJGMC,ZYBC,
		ZYSZGCJG,YZNR,BZLZ,ZYJZFF,ZYYYFF,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ
	FROM #TEMP_GXBZ_EMR_RCBCJL
	
	COMMIT TRAN
	
		SELECT 'T' AS SRESULT, '表：GXBZ_EMR_RCBCJL数据抽取成功！' AS SHINT

		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
		VALUES('表：GXBZ_EMR_RCBCJL数据抽取成功','GXBZ_EMR_RCBCJL',GETDATE(),'T','USP_WD_GXBZ_EMR_RCBCJL')
		
			--更新上传时间
			UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_RCBCJL'
			
	END TRY BEGIN CATCH
		SELECT 'F' AS SRESULT, '表：GXBZ_EMR_RCBCJL数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
		ROLLBACK TRAN
		
		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_RCBCJL数据抽取失败','GXBZ_EMR_RCBCJL',GETDATE(),'F','USP_GXBZ_EMR_RCBCJL',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END